package com.jgp.attachment.utils;


import com.jgp.common.utils.JGPUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * 项目   jgp-cloud-parent
 * 作者   loufei
 * 时间   2018/9/2
 * <p>
 * Workbook workbook = OfficeExcelUtil.readExcel("/Users/congcong/Public/projects/temp/test.xlsx");
 * <p>
 * public class UserInfo {<br>
 * &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 * ...<br>
 * &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 * private Boolean sex;<br>
 * &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 * ...<br>
 * }
 * <p>
 * Map&lt;String, Function&lt;String,?&gt;&gt; mapper = new HashMap&lt;&gt;();<br>
 * mapper.put("sex", (cellValue) -> cellValue.equals("男")?true:false);<br>
 * List&lt;UserInfo&gt; listMap = OfficeExcelUtil.transToListBean(workbook.getSheetAt(0), UserInfo.class,0,
 * 1,mapper);<br><br>
 * List&lt;Map&lt;String,String&gt;&gt; list = OfficeExcelUtil.transToListMap(workbook.getSheetAt(0), 0 );<br><br>
 * List&lt;Map&lt;String,String&gt;&gt; list2 = OfficeExcelUtil.transToListMap(workbook.getSheetAt(0));<br><br>
 * </p>
 */
public class OfficeExcelUtil {
    
    /**
     * 读取Excel
     *
     * @param filePath 文件物理路径
     * @return Workbook {@link Workbook}
     */
    public static Workbook readExcel(String filePath) {
        Workbook wb = null;
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return new XSSFWorkbook(is);
            } else {
                return null;
            }
            
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }
    
    /**
     * 获取Sheet个数
     *
     * @param workbook
     * @return
     */
    public static int getSheetSize(Workbook workbook) {
        return workbook.getNumberOfSheets();
    }
    
    /**
     * 读取EXCEL
     *
     * @param sheet      工作薄
     * @param clazz      目标类型
     * @param headLineNo 表头行 行号 若没有表头 设置为 null
     * @param keyLineNo  标识行 行号 此行数据应与实体内字段一致
     * @param mapper     Map<{@link String}, {@link Function}> 列映射，将列映射为指定类型或指定内容，当设置为null时，<br>
     *                   转换出来的列默认支持 {@link Boolean} {@link String} {@link Double}，<br>
     *                   若对应实体中有别的类型需要映射转换。<br>
     *                   key:需要转换的列标识（实体中的属性）value:转换为函数式接口（转换方式）  <br>
     *                   <p>
     *                   例如: 目标类型中有个属性为 createDatetime 类型为 {@link java.time.LocalDateTime} <br>
     *                   Map<String,Function> mapper = new HashMap<>();<br>
     *                   mapper.put("createDatetime",(cellValue)->{<br>
     *                   &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
     *                   //cellValue 是从Excel中读取的单元格数据 要转换为{@link java.time.LocalDateTime} 并返回<br>
     *                   &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
     *                   return LocalDateTime.parse(cellValue+"");<br>
     *                   });
     * @param <T>        目标类型
     * @return List&lt;T&gt;
     */
    public static <T> List<T> transToListBean(Sheet sheet, Class<T> clazz, Integer headLineNo, Integer
            keyLineNo, Map<String, Function<String, ?>> mapper) {
        List<T> result = new ArrayList<>();
        List<Map<String, Object>> list = transToListMap(sheet, headLineNo, keyLineNo, mapper);
        for (Map<String, Object> map : list) {
            T bean = JGPUtil.toBean(map, clazz);
            result.add(bean);
        }
        return result;
    }
    
    /**
     * 读取EXCEL
     *
     * @param sheet 工作薄
     * @return List&lt;Map&lt;String,Object&gt;&gt;
     */
    public static List<Map<String, String>> transToListMap(Sheet sheet) {
        return transToListMap(sheet, null, null);
    }
    
    /**
     * 读取EXCEL
     *
     * @param sheet      工作薄
     * @param headLineNo 表头行 行号 若没有表头 设置为 null
     * @return List&lt;Map&lt;String,Object&gt;&gt;
     */
    public static List<Map<String, String>> transToListMap(Sheet sheet, Integer headLineNo) {
        return transToListMap(sheet, headLineNo, null);
    }
    
    
    /**
     * 读取EXCEL
     *
     * @param sheet      工作薄
     * @param headLineNo 表头行 行号 若没有表头 设置为 null
     * @param keyLineNo  标识行 行号 此行数据应与实体内字段一致
     * @return List&lt;Map&lt;String,Object&gt;&gt;
     */
    public static List<Map<String, String>> transToListMap(Sheet sheet, Integer headLineNo, Integer
            keyLineNo) {
        return transToListMap(sheet, headLineNo, keyLineNo, null).stream().map(map -> {
            HashMap<String, String> m = new HashMap<>();
            map.forEach((s, o) -> m.put(s, Objects.nonNull(o)?o.toString():null));
            return m;
        }).collect(Collectors.toList());
    }
    
    private static List<Map<String, Object>> transToListMap(Sheet sheet, Integer headLineNo, Integer
            keyLineNo, Map<String, Function<String, ?>> mapper) {
        int rowSize = sheet.getPhysicalNumberOfRows();
        List<Map<String, Object>> records = new ArrayList<>();
        Map<Integer, String> mapperKeys = Objects.nonNull(keyLineNo) ? getMapperKey(sheet, keyLineNo) : null;
        for (int r = 0; r <= rowSize; r++) {
            if (Objects.nonNull(headLineNo) && r == headLineNo) continue;
            if (Objects.nonNull(keyLineNo) && r == keyLineNo) continue;
            Row row = sheet.getRow(r);
            if (Objects.isNull(row)) continue;
            int cellSize = row.getPhysicalNumberOfCells();
            Map<String, Object> record = new HashMap<>();
            
            for (int c = 0; c <= cellSize; c++) {
                Cell cell = row.getCell(c);
                if (Objects.isNull(cell)) continue;
                String key = Objects.nonNull(mapperKeys) ? mapperKeys.get(c) : Integer.toString(c);
                if (Objects.nonNull(mapper)) {
                    Function function = mapper.get(key);
                    if (Objects.nonNull(function)) {
                        record.put(key, mapper.get(key).apply(getValue(cell)));
                        continue;
                    }
                }
                record.put(key, getValue(cell));
            }
            records.add(record);
        }
        return records;
    }
    
    private static Map<Integer, String> getMapperKey(Sheet sheet, int keyIndex) {
        Map<Integer, String> keys = new HashMap<>();
        sheet.forEach(row -> {
            row.forEach(cell -> {
                if (cell.getRowIndex() == keyIndex) {
                    int c = cell.getColumnIndex();
                    String key = getValue(cell).toString();
                    keys.put(c, key);
                }
            });
        });
        return keys;
    }
    
    private static String getValue(Cell cell) {
        String value = null;
        switch (cell.getCellType()) {
            case NUMERIC:
                value = Double.toString(cell.getNumericCellValue());
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = Boolean.toString(cell.getBooleanCellValue());
                break;
            case BLANK:
                break;
        }
        return value;
    }
    
    public class SheetFormatHandler {
        private Class<?> clazz;
        private Integer headLineNo;
        private Integer keyLineNo;
        
        public SheetFormatHandler(Class<?> clazz, Integer headLineNo, Integer keyLineNo) {
            this.clazz = clazz;
            this.headLineNo = headLineNo;
            this.keyLineNo = keyLineNo;
        }
        
        public Class<?> getClazz() {
            return clazz;
        }
        
        public void setClazz(Class<?> clazz) {
            this.clazz = clazz;
        }
        
        public Integer getHeadLineNo() {
            return headLineNo;
        }
        
        public void setHeadLineNo(Integer headLineNo) {
            this.headLineNo = headLineNo;
        }
        
        public Integer getKeyLineNo() {
            return keyLineNo;
        }
        
        public void setKeyLineNo(Integer keyLineNo) {
            this.keyLineNo = keyLineNo;
        }
    }
}
